package icu.groot.alan.dao.impl;

import icu.groot.alan.dao.UserDao;
import icu.groot.alan.entity.*;
import icu.groot.alan.util.Dao;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public LoginUser login(int code, Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        LoginUser loginUser = null;
        Object[] params = {code};
        String sql = "SELECT u.`code` `code`, u.user_name userName, u.`password` `password`, u.introduction introduction, d.`name` department  FROM `user` u LEFT JOIN `department` d ON u.department_id = d.id WHERE u.`code` = ?";
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
            while (resultSet.next()) {
                loginUser = new LoginUser();
                loginUser.setCode(resultSet.getInt("code"));
                loginUser.setUserName(resultSet.getString("userName"));
                loginUser.setPassword(resultSet.getString("password"));
                loginUser.setDepartment(resultSet.getString("department"));
                loginUser.setIntroduction(resultSet.getString("introduction"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
        Dao.closeResource(preparedStatement, resultSet);
        return loginUser;
    }

    @Override
    public OneselfUser oneself(int code, Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        OneselfUser oneselfUser = null;
        Object[] params = {code};
        String sql = "SELECT u.`code` `code`, u.user_name userName,u.birth_date birthDate, u.education education, u.degree degree, u.gender gender, u.introduction introduction, u.job_title jobTitle, u.email email, u.qq qq, d.`name` department  FROM `user` u LEFT JOIN `department` d ON u.department_id = d.id WHERE u.`code` = ?";
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
            while (resultSet.next()) {
                oneselfUser = new OneselfUser();
                oneselfUser.setCode(resultSet.getInt("code"));
                oneselfUser.setUserName(resultSet.getString("userName"));
                oneselfUser.setBirthDate(resultSet.getString("birthDate"));
                oneselfUser.setEducation(resultSet.getString("education"));
                oneselfUser.setDegree(resultSet.getString("degree"));
                oneselfUser.setGender(resultSet.getString("gender"));
                oneselfUser.setIntroduction(resultSet.getString("introduction"));
                oneselfUser.setJobTitle(resultSet.getString("jobTitle"));
                oneselfUser.setEmail(resultSet.getString("email"));
                oneselfUser.setQq(resultSet.getString("qq"));
                oneselfUser.setDepartment(resultSet.getString("department"));
            }
        } catch (SQLException e) {
            return null;
        }
        Dao.closeResource(preparedStatement, resultSet);
        return oneselfUser;
    }

    @Override
    public List<String> getDepartmentByCode(Connection connection, int code) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<String> department = new ArrayList<>();
        Object[] params = {code};
        String sql = "SELECT d.`name` department  FROM `user` u LEFT JOIN `department` d ON u.department_id = d.id WHERE u.`code` = ?";
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
            while (resultSet.next()) {
                department.add(resultSet.getString("department"));
            }
            //测试是否有数据
            department.get(0);
        } catch (SQLException e) {
            return null;
        }
        Dao.closeResource(preparedStatement, resultSet);
        return department;
    }

    @Override
    public int updateDepartmentByDepId(Connection connection, int[] idsInt) {
        PreparedStatement preparedStatement = null;
        int updateRow = 0;
        Object[] params = {};
        StringBuilder sql = new StringBuilder("UPDATE user SET department_id = 123456 where department_id in ( ");
        for (int i = 0; i < idsInt.length; i++) {
            if (i != idsInt.length - 1){
                sql.append(" ").append(idsInt[i]).append(", ");
                continue;
            }
            sql.append(idsInt[i]).append(" )");
        }
        try {
            updateRow = Dao.execute(connection, preparedStatement, sql.toString(), params);
        } catch (SQLException e) {
            return 0;
        }
        Dao.closeResource(preparedStatement);
        return updateRow;
    }

    @Override
    public int getCount(Connection connection, String fuzzyQuery, int departmentId) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int rowCount = 0;
        List<Object> params = new ArrayList<>();
        String sql = "SELECT count(*) rowCount from user where 1 = 1";
        if (departmentId > 0){
            sql += "  and department_id = ? ";
            params.add(departmentId);
        }
        if (!StringUtils.isAnyBlank(fuzzyQuery)){
            sql += " and `code` like ? or `user_name` like ? ";
            params.add("%" + fuzzyQuery + "%");
            params.add("%" + fuzzyQuery + "%");
            if (departmentId > 0){
                sql += "  and department_id = ? ";
                params.add(departmentId);
            }
        }
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params.toArray());
            while (resultSet.next()) {
                rowCount = resultSet.getInt("rowCount");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
        Dao.closeResource(preparedStatement, resultSet);
        return rowCount;
    }

    @Override
    public void paging(Paging<OneselfUser> paging, Connection connection) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        OneselfUser oneselfUser = null;
        List<OneselfUser> oneselfUsers = new ArrayList<>();
        List<Object> params = new ArrayList<>();

        String sql = "SELECT * FROM `user` u LEFT JOIN `department` d ON u.department_id = d.id WHERE 1 = 1";
        if (paging.getDepartmentId() > 0){
            sql += "  and u.department_id = ? ";
            params.add(paging.getDepartmentId());
        }
        if (!StringUtils.isAnyBlank(paging.getFuzzyQuery())){
            sql += " and u.`code` like ? or u.`user_name` like ? ";
            params.add("%" + paging.getFuzzyQuery() + "%");
            params.add("%" + paging.getFuzzyQuery() + "%");
            if (paging.getDepartmentId() > 0){
                sql += "  and u.department_id = ? ";
                params.add(paging.getDepartmentId());
            }
        }
        int pageNow = paging.getPageNow();
        if (pageNow < 0 || pageNow >= paging.getPageCount()){
            pageNow = 0;
        }
        sql += " LIMIT ?, ? ";
        params.add((paging.getPageNow() - 1) * paging.getPageSize());
        params.add(paging.getPageSize());
        try {
            resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params.toArray());
            while (resultSet.next()) {
                oneselfUser = new OneselfUser();
                oneselfUser.setCode(resultSet.getInt("code"));
                oneselfUser.setUserName(resultSet.getString("user_name"));
                oneselfUser.setBirthDate(resultSet.getString("birth_date"));
                oneselfUser.setEducation(resultSet.getString("education"));
                oneselfUser.setDegree(resultSet.getString("degree"));
                oneselfUser.setGender(resultSet.getString("gender"));
                oneselfUser.setIntroduction(resultSet.getString("introduction"));
                oneselfUser.setJobTitle(resultSet.getString("job_title"));
                oneselfUser.setEmail(resultSet.getString("email"));
                oneselfUser.setQq(resultSet.getString("qq"));
                oneselfUser.setDepartment(resultSet.getString("name"));
                oneselfUser.setPassword(resultSet.getString("password"));
                oneselfUsers.add(oneselfUser);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            paging.setData(null);
        }
        paging.setData(oneselfUsers);
        Dao.closeResource(preparedStatement, resultSet);
    }

    @Override
    public int add(String name, String gender, String birthDate, String jobTitle, String education, String degree, String qq, String email, String introduction, String departmentId, String password, Connection connection) throws SQLException {

        PreparedStatement preparedStatement = null;
        Object[] params = {name, gender, birthDate, jobTitle, education, degree, qq, email, introduction, departmentId, password};
        String sql = "insert into user(user_name, gender, birth_date, job_title, education, degree, qq, email, introduction, department_id, password) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        int updateRow = Dao.execute(connection, preparedStatement, sql, params);
        Dao.closeResource(preparedStatement);
        return updateRow;
    }

    @Override
    public OneselfUser getUserByCode(Connection connection, int code) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        OneselfUser oneselfUser = null;
        Object[] params = {code};
        String sql = "SELECT * FROM `user` u LEFT JOIN `department` d ON u.department_id = d.id where code = ?";
        resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
        while (resultSet.next()) {
            oneselfUser = new OneselfUser();
            oneselfUser.setCode(resultSet.getInt("code"));
            oneselfUser.setUserName(resultSet.getString("user_name"));
            oneselfUser.setBirthDate(resultSet.getString("birth_date"));
            oneselfUser.setEducation(resultSet.getString("education"));
            oneselfUser.setDegree(resultSet.getString("degree"));
            oneselfUser.setGender(resultSet.getString("gender"));
            oneselfUser.setIntroduction(resultSet.getString("introduction"));
            oneselfUser.setJobTitle(resultSet.getString("job_title"));
            oneselfUser.setEmail(resultSet.getString("email"));
            oneselfUser.setQq(resultSet.getString("qq"));
            oneselfUser.setDepartment(resultSet.getString("name"));
            oneselfUser.setPassword(resultSet.getString("password"));
        }
        Dao.closeResource(preparedStatement,resultSet);
        return oneselfUser;
    }

    @Override
    public int deleted(Connection connection, int[] codes) throws SQLException {
        PreparedStatement preparedStatement = null;
        Object[] params = {};
        StringBuilder sql = new StringBuilder("DELETE FROM user WHERE code in (");
        for (int i = 0; i < codes.length; i++) {
            if (i != codes.length - 1){
                sql.append(" ").append(codes[i]).append(", ");
                continue;
            }
            sql.append(codes[i]).append(" )");
        }
        int updateRow = Dao.execute(connection, preparedStatement, sql.toString(), params);
        Dao.closeResource(preparedStatement);
        return updateRow;
    }

    @Override
    public int updateUserByCode(Connection connection, int code, String name, String gender, String birthDate, String jobTitle, String education, String degree, String qq, String email, String introduction, String departmentId, String password) throws SQLException {
        PreparedStatement preparedStatement = null;
        Object[] params = {name, gender, birthDate, jobTitle, education, degree, qq, email, introduction, departmentId, password,  code};
        String sql = "UPDATE user SET user_name = ?, gender = ?, birth_date = ?, job_title = ?, education = ?, degree = ?, qq = ?, email = ?, introduction = ?, department_id = ?, password = ? where code = ?";
        int updateRow = Dao.execute(connection, preparedStatement, sql, params);
        Dao.closeResource(preparedStatement);
        return updateRow;
    }
}
